<?php
include_once("../info_struct.php");

/*
 * Usage:
* infos2SqlInsert(ref);
* Parameter:
* ref - infos object to be inserted into local database
* Return:
* SQL code for inserting the infos object into local database
*/
function infos_2_sql_insert($ref) {
    $title = $ref->title;
    if (strpos($ref->year, " BC")) {
        $ref->year = substr($ref->year, 0, -3);
    }
    $year = $ref->bc  ? $ref->year . " BC" : $ref->year;
    $author = $ref->author;
    $doi = $ref->doi;
    $zmath = $ref->zmath;
    $arxiv = $ref->arxiv;
    $journal = $ref->journal;
    $volume = $ref->volume;
    $number = $ref->number;
    $pages = $ref->pages;
    $entered_by = $ref->entered_by;


    $sql = "INSERT INTO `reference`(`title`, `year`, `author`, `doi`"
        .", `zmath`, `arxiv`, `journal`, `volume`, `number`, `pages`,
        `entered_by`, `entered_at`)
        VALUES ('$title', '$year', '$author', '$doi', '$zmath', '$arxiv',
        '$journal', '$volume', '$number', '$pages', '$entered_by', NOW())";

    return $sql;
}

/*
 * Usage:
 * infos_2_sql_insert_xml(ref);
 * Parameter:
 * ref - infos object containing the reference that is to be added to the
 *     local database (must contain an ref_id)
 * Return:
 * Returns the SQL-query for adding the reference to the local database.
 * 
 * IMPORTANT: Intended to be used only for the XML Export.
 */
function infos_2_sql_insert_xml($ref) {
    $sql ="INSERT INTO `reference` (`ref_id`, `title`, `year`, `author`, `doi`"
    .", `zmath`, `arxiv`, `journal`, `volume`, `number`, `pages`,
    `entered_at`)
    VALUES ($ref->id, '$ref->title', '$ref->year', '$ref->author',
        '$ref->doi', '$ref->zmath', '$ref->arxiv',
    '$ref->journal', '$ref->volume', '$ref->number', '$ref->pages', NOW())";
    
    return $sql;
}

/*
 * Usage:
 * note_2_sql_insert(ref);
 * Parameter:
 * ref - infos object that contains the id of the reference and the note
 *     that is to be added for this reference
 * Return:
 * SQL-query for adding the note to the database
 */
function note_2_sql_insert($ref) {
    $sql = "INSERT INTO `ref_note` (`ref_id`, `title`, `note`)
        VALUES($ref->id, '$ref->note_title', '$ref->note')";
    
    return $sql;
}

function note_2_sql_update($ref) {
    $sql = "UPDATE `ref_note`
        SET `title` = '$ref->note_title', `note` = '$ref->note'
        WHERE `ref_note_id` = $ref->note_id";
    
    return $sql;
}

/*
 * Usage:
 * id_2_sql_search(id);
 * Parameter:
 * id - ref_id of the reference that is to be searched
 * Return:
 * SQL-query for searching the database for the wanted reference.
 * IMPORTANT: Intended to be used only in the Tooltip function.
 */
function id_2_sql_get_by_id($id) {
    $sql = "SELECT `reference`.ref_id, `reference`.title, `reference`.year,
        `reference`.author, `reference`.doi, `reference`.zmath,
        `reference`.arxiv, `reference`.journal, `reference`.volume,
        `reference`.number, `reference`.pages, `reference`.entered_by,
        `reference`.entered_at, `ref_note`.ref_note_id,
        `ref_note`.title AS note_title, `ref_note`.note
        FROM `ref_note` RIGHT JOIN `reference`
        ON `ref_note`.ref_id = `reference`.ref_id
        WHERE `reference`.ref_id = $id";
    return $sql;
}


/*
 * Usage:
* infos_2_sql_search_exact(ref);
* Parameter:
* ref - infos object for which we want to search in the local database
* Return:
* SQL code for search after entry with same title, author and year as ref
*/
function infos_2_sql_search_exact($ref) {
    $sql = "SELECT `reference`.ref_id, `reference`.title, `reference`.year,
        `reference`.author, `reference`.doi, `reference`.zmath,
        `reference`.arxiv, `reference`.journal, `reference`.volume,
        `reference`.number, `reference`.pages, `reference`.entered_by,
        `reference`.entered_at, `ref_note`.ref_note_id,
        `ref_note`.title AS note_title, `ref_note`.note
        FROM `ref_note` RIGHT JOIN `reference`
        ON `ref_note`.ref_id = `reference`.ref_id
        WHERE `reference`.`title` =  '$ref->title' AND "
        ."`reference`.`author` = '$ref->author' AND 
        `reference`.`year` = '$ref->year'";

    return $sql;
}

/*
 * Usage:
 * infos_2_sql_search_doi(ref);
 * Parameter:
 * ref - infos object containing a doi
 * Return:
 * SQL-query for searching for entries with the given doi
 */
function infos_2_sql_search_doi($ref) {
    $sql = "SELECT `reference`.ref_id, `reference`.title, `reference`.year,
        `reference`.author, `reference`.doi, `reference`.zmath,
        `reference`.arxiv, `reference`.journal, `reference`.volume,
        `reference`.number, `reference`.pages, `reference`.entered_by,
        `reference`.entered_at, `ref_note`.ref_note_id,
        `ref_note`.title AS note_title, `ref_note`.note
        FROM `ref_note` RIGHT JOIN `reference`
        ON `ref_note`.ref_id = `reference`.ref_id
        WHERE `reference`.`doi` = '$ref->doi'";
    return $sql;
}

/*
 * Usage:
 * infos_2_sql_search_zmath(ref);
 * Parameter:
 * ref - infos object containing a zmath-id
 * Return:
 * SQL-query for searching for entries with the given zmath-id
 */
function infos_2_sql_search_zmath($ref) {
    $sql = "SELECT `reference`.ref_id, `reference`.title, `reference`.year,
        `reference`.author, `reference`.doi, `reference`.zmath,
        `reference`.arxiv, `reference`.journal, `reference`.volume,
        `reference`.number, `reference`.pages, `reference`.entered_by,
        `reference`.entered_at, `ref_note`.ref_note_id,
        `ref_note`.title AS note_title, `ref_note`.note
        FROM `ref_note` RIGHT JOIN `reference`
        ON `ref_note`.ref_id = `reference`.ref_id
        WHERE `reference`.`zmath` = '$ref->zmath'";
    return $sql;
}

/*
 * Usage:
 * infos_2_sql_search_arxiv(ref);
 * Parameter:
 * ref - infos object containing an arxiv-id
 * Return:
 * SQL-query for searching for entries with the given arxiv-id
 */
function infos_2_sql_search_arxiv($ref) {
    $sql = "SELECT `reference`.ref_id, `reference`.title, `reference`.year,
        `reference`.author, `reference`.doi, `reference`.zmath,
        `reference`.arxiv, `reference`.journal, `reference`.volume,
        `reference`.number, `reference`.pages, `reference`.entered_by,
        `reference`.entered_at, `ref_note`.ref_note_id,
        `ref_note`.title AS note_title, `ref_note`.note
        FROM `ref_note` RIGHT JOIN `reference`
        ON `ref_note`.ref_id = `reference`.ref_id
        WHERE `reference`.`arxiv` = '$ref->arxiv'";
    return $sql;
}

/*
 * Usage:
 * sql_result_2_infos(result);
 * Parameter:
 * result - Result provided by an SQL-query
 * Return:
 * Array of infos objects
 */
function sql_result_2_infos($result) {
    $ret = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $ref = new infos();
        $ref->id = $row['ref_id'];
        $ref->title = $row['title'];
        $ref->author = $row['author'];
        $ref->year = $row['year'];
        $ref->bc = (substr_count($ref->year, "BC") > 0) ? true : false;
        $ref->journal = $row['journal'];
        $ref->note_title = $row['note_title'];
        $ref->note = $row['note'];
        $ref->note_id = $row['ref_note_id'];
        $ref->doi = $row['doi'];
        $ref->zmath = $row['zmath'];
        $ref->arxiv = $row['arxiv'];
        $ref->number = $row['number'];
        $ref->volume = $row['volume'];
        $ref->pages = $row['pages'];
        
        $ref->from = "ldb";
        
        array_push($ret, $ref);
    }
    
    return $ret;
}


/*
 * Usage:
* infos2SqlUpdate(ref);
* Parameter:
* ref - infos object containing reference that is to be updated in the local
* database. ref must contain an id!
* Return:
* SQL code for updating the according reference in the database
*/
function infos_2_sql_update($ref) {
    if ($ref->id) {
        $sql = "UPDATE `reference`
            SET `title` = '$ref->title', `year` = '$ref->year',
            `author` = '$ref->author', `doi` = '$ref->doi',
            `zmath` = '$ref->zmath', `arxiv` = '$ref->arxiv',
            `journal` = '$ref->journal', `volume` = $ref->volume,
            `number` = $ref->number, `pages` = '$ref->pages', 
            `entered_at` = NOW()
            WHERE `reference`.`ref_id` = '$ref->id';";
        

        return $sql;
    }
}

/*
 * Usage:
 * query_list_from_to(from, to);
 * Parameter:
 * from - Starting id of reference that is to be retrieved
 * to - Last id that is to be retrieved
 * Return:
 * SQL-query for getting a list of references from the local database
 * 
 * IMPORTANT: Intended for use by the list function. Currently not in use.
 */
function query_list_from_to($from, $to) {
    $sql = "SELECT `reference`.ref_id, `reference`.title, `reference`.year,
        `reference`.author, `reference`.doi, `reference`.zmath,
        `reference`.arxiv, `reference`.journal, `reference`.volume,
        `reference`.number, `reference`.pages, `reference`.entered_by,
        `reference`.entered_at, `ref_note`.ref_note_id,
        `ref_note`.title AS note_title, `ref_note`.note
        FROM `ref_note` RIGHT JOIN `reference`
        ON `ref_note`.ref_id = `reference`.ref_id
        WHERE `reference`.ref_id >= $from AND `reference`.ref_id <= $to
        ORDER BY `reference`.ref_id ASC";
    
    return $sql;
}

function query_list_full() {
    $sql = "SELECT `reference`.ref_id, `reference`.title, `reference`.year,
        `reference`.author, `reference`.doi, `reference`.zmath,
        `reference`.arxiv, `reference`.journal, `reference`.volume,
        `reference`.number, `reference`.pages, `reference`.entered_by,
        `reference`.entered_at, `ref_note`.ref_note_id,
        `ref_note`.title AS note_title, `ref_note`.note
        FROM `ref_note` RIGHT JOIN `reference`
        ON `ref_note`.ref_id = `reference`.ref_id
        ORDER BY `reference`.ref_id ASC";
    
    return $sql;
}

/*
 * Usage:
 * escape_ref(ref);
 * Parameter:
 * ref - Reference that is to be escaped
 * Return:
 * Returns infos object with escaped attributes
 * to harden the system against attacks
 */
function escape_ref($ref) {
    $ref->title = escape_input($ref->title);
    $ref->author = escape_input($ref->author);
    $ref->year = escape_input($ref->year);
    $ref->journal = escape_input($ref->journal);
    $ref->doi = escape_input($ref->doi);
    $ref->zmath = escape_input($ref->zmath);
    $ref->arxiv = escape_input($ref->arxiv);
    $ref->volume = escape_input($ref->volume);
    $ref->number = escape_input($ref->number);
    $ref->pages = escape_input($ref->pages);
    $ref->note_title = escape_input($ref->note_title);
    $ref->note = escape_input($ref->note);
    
}

/*
 * Usage:
 * escape_input(data);
 * Parameter:
 * data - string that is to be escaped
 * Return:
 */
function escape_input($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    $data = mysql_real_escape_string($data);
    return $data;
}

?>